Imports PTT.CarRes.Data

Public Class ClassQueryInvReserveDataCarDriver
    Public Function Add(ByVal InvReserveDataCarDriver As QueryInvReserveDataCarDriver, ByVal StrConn As String) As Boolean
        Dim ClsInvReserveDataCarDriver As New ClassInvReserveDataCarDriver
        Dim Conn As OleDb.OleDbConnection
        Conn = ClsInvReserveDataCarDriver.ConnectionOpen(StrConn)
        Dim ret As Boolean = False
        With ClsInvReserveDataCarDriver
            .BdNumber = InvReserveDataCarDriver.BdNumber
            .StartMile = InvReserveDataCarDriver.StartMile
            .StopMile = InvReserveDataCarDriver.StopMile
            .FuelDesc = InvReserveDataCarDriver.FuelDesc
            .FuelAmount = InvReserveDataCarDriver.FuelAmount
            .EstiMate = InvReserveDataCarDriver.EstiMate
            .WorkCost = InvReserveDataCarDriver.WorkCost
            .CostWorkStatus = InvReserveDataCarDriver.CostWorkStatus
            .ReserveId = InvReserveDataCarDriver.ReserveId
            .CarId = InvReserveDataCarDriver.CarId
            .DriverCode = InvReserveDataCarDriver.DriverCode
            .FuelId = InvReserveDataCarDriver.FuelId
            ret = .USP_Save(Conn)
        End With
        ClsInvReserveDataCarDriver.ConnectionClose(Conn)
        Return ret
    End Function

    Public Function Edit(ByVal InvReserveDataCarDriver As QueryInvReserveDataCarDriver, ByVal StrConn As String) As Boolean
        Dim ClsInvReserveDataCarDriver As New ClassInvReserveDataCarDriver
        Dim Conn As OleDb.OleDbConnection
        Conn = ClsInvReserveDataCarDriver.ConnectionOpen(StrConn)
        Dim ret As Boolean = False
        With ClsInvReserveDataCarDriver
            .BdNumber = InvReserveDataCarDriver.BdNumber
            .Load(Conn)
            .StartMile = InvReserveDataCarDriver.StartMile
            .StopMile = InvReserveDataCarDriver.StopMile
            .FuelDesc = InvReserveDataCarDriver.FuelDesc
            .FuelAmount = InvReserveDataCarDriver.FuelAmount
            .EstiMate = InvReserveDataCarDriver.EstiMate
            .WorkCost = InvReserveDataCarDriver.WorkCost
            .CostWorkStatus = InvReserveDataCarDriver.CostWorkStatus
            .ReserveId = InvReserveDataCarDriver.ReserveId
            .CarId = InvReserveDataCarDriver.CarId
            .DriverCode = InvReserveDataCarDriver.DriverCode
            .FuelId = InvReserveDataCarDriver.FuelId
            ret = .USP_Update(Conn)
        End With
        ClsInvReserveDataCarDriver.ConnectionClose(Conn)
        Return ret
    End Function
    Public Function Edit_CloseWork(ByVal InvReserveInfo As QueryInvReserveInfo, ByVal InvDataTableDataCloseWork As DataSet, ByVal InvDataTableDataTime As DataSet, ByVal StrConn As String) As Boolean
        Dim ClsInvReserveDataCarDriver As New ClassInvReserveDataCarDriver
        Dim ClsInvReserveTimeClostWork As New ClassInvReserveTimeClostWork
        Dim ClsInvReserveInfo As New ClassInvReserveInfo
        Dim ii As Integer
        Dim xCount As Integer
        Dim xCount1 As Integer
        Dim Con As OleDb.OleDbConnection
        Con = ClsInvReserveDataCarDriver.ConnectionOpen(StrConn)
        Dim Conn As OleDb.OleDbConnection
        Dim VTran As OleDb.OleDbTransaction
        Conn = ClsInvReserveDataCarDriver.ConnectionOpen(StrConn)
        VTran = ClsInvReserveDataCarDriver.BeginTran(Conn)
        Dim rEt As Boolean = False
        With ClsInvReserveInfo
            .ReserveId = InvReserveInfo.ReserveId
            .Load(Con)
            .ReserveDate = InvReserveInfo.ReserveDate
            .UserId = InvReserveInfo.UserId
            .UnitId = InvReserveInfo.UnitId
            .AgentId = InvReserveInfo.AgentId
            .Telephone = InvReserveInfo.Telephone
            .CarAmount = InvReserveInfo.CarAmount
            .StartDate = InvReserveInfo.StartDate
            .StopDate = InvReserveInfo.StopDate
            .StatusDriver = InvReserveInfo.StatusDriver
            .HumanAmount = InvReserveInfo.HumanAmount
            .Area = InvReserveInfo.Area
            .Remark = InvReserveInfo.Remark
            .Description = InvReserveInfo.Description
            .UserStatus = InvReserveInfo.UserStatus
            .ReserveCost = InvReserveInfo.ReserveCost
            .ApproveId = InvReserveInfo.ApproveId
            .ApproveStatus = InvReserveInfo.ApproveStatus
            .ApprovedDate = InvReserveInfo.ApprovedDate
            .ApproveRemark = InvReserveInfo.ApproveRemark
            .ApproveBdStatus = InvReserveInfo.ApproveBdStatus
            .ApproveBdDate = InvReserveInfo.ApproveBdDate
            .ApproveBdRemark = InvReserveInfo.ApproveBdRemark
            .FlageReserve = InvReserveInfo.FlageReserve
            .ApproveBdId = InvReserveInfo.ApproveBdId
            .WareaGroupId = InvReserveInfo.WareaGroupId
            .DataTypeId = InvReserveInfo.DataTypeId
            .TypeId = InvReserveInfo.TypeId
            .PersonNo = InvReserveInfo.PersonNo
            rEt = .USP_Update(Conn, VTran)
        End With

        If rEt = True Then
            With ClsInvReserveDataCarDriver
                xCount = InvDataTableDataCloseWork.Tables(0).Rows.Count - 1
                For ii = 0 To xCount
                    .BdNumber = InvDataTableDataCloseWork.Tables(0).Rows(ii).Item("BdNumber")
                    .Load(Con)
                    .StartMile = InvDataTableDataCloseWork.Tables(0).Rows(ii).Item("StartMile")
                    .StopMile = InvDataTableDataCloseWork.Tables(0).Rows(ii).Item("StopMile")
                    .FuelDesc = InvDataTableDataCloseWork.Tables(0).Rows(ii).Item("FuelDesc")
                    .FuelAmount = InvDataTableDataCloseWork.Tables(0).Rows(ii).Item("FuelAmount")
                    .EstiMate = InvDataTableDataCloseWork.Tables(0).Rows(ii).Item("EstiMate")
                    .WorkCost = InvDataTableDataCloseWork.Tables(0).Rows(ii).Item("WorkCost")
                    .CostWorkStatus = InvDataTableDataCloseWork.Tables(0).Rows(ii).Item("CostWorkStatus")
                    .ReserveId = InvDataTableDataCloseWork.Tables(0).Rows(ii).Item("ReserveId")
                    .CarId = InvDataTableDataCloseWork.Tables(0).Rows(ii).Item("CarId")
                    .DriverCode = InvDataTableDataCloseWork.Tables(0).Rows(ii).Item("DriverCode")
                    .FuelId = InvDataTableDataCloseWork.Tables(0).Rows(ii).Item("FuelId")
                    rEt = .USP_Update(Conn, VTran)
                    If rEt = False Then
                        Exit For
                    End If
                Next
            End With

            If rEt = True Then
                With ClsInvReserveTimeClostWork
                    '.ReserveId = InvDataTableDataTime.Tables(0).Rows(0).Item("ReserveId")
                    'rEt = .USP_Delete(Conn, VTran)
                    If rEt = True Then
                        xCount1 = InvDataTableDataTime.Tables(0).Rows.Count - 1
                        For ii = 0 To xCount1
                            .DayId = ii + 1
                            .BdNumber = InvDataTableDataTime.Tables(0).Rows(ii).Item("ReserveId")
                            .ReserveDay = InvDataTableDataTime.Tables(0).Rows(ii).Item("ReserveDayInsert")
                            .StartTime = InvDataTableDataTime.Tables(0).Rows(ii).Item("StartTime")
                            .StopTime = InvDataTableDataTime.Tables(0).Rows(ii).Item("StopTime")
                            rEt = .USP_Save(Conn, VTran)
                        Next
                        If rEt = True Then
                            ClsInvReserveDataCarDriver.CommitTran(VTran)
                        Else
                            ClsInvReserveDataCarDriver.RollBackTran(VTran)
                        End If
                    Else
                        ClsInvReserveDataCarDriver.RollBackTran(VTran)
                    End If
                End With
            Else
                ClsInvReserveDataCarDriver.RollBackTran(VTran)
            End If

        Else
            ClsInvReserveDataCarDriver.RollBackTran(VTran)
        End If
        ClsInvReserveDataCarDriver.ConnectionClose(Con)
        ClsInvReserveDataCarDriver.ConnectionClose(Conn)
        Return rEt
    End Function

    Public Function BdApproveData(ByVal InvReserveInfo As QueryInvReserveInfo, ByVal DataCarDriver As DataSet, ByVal StrConn As String) As Boolean
        Dim ClsInvReserveDataCarDriver As New ClassInvReserveDataCarDriver
        Dim ClsInvReserveDataTime As New ClassInvReserveDataTime
        Dim ClsInvReserveInfo As New ClassInvReserveInfo
        Dim ii As Integer
        Dim xCount As Integer
        Dim xCount1 As Integer
        Dim Con As OleDb.OleDbConnection
        Con = ClsInvReserveDataCarDriver.ConnectionOpen(StrConn)
        Dim Conn As OleDb.OleDbConnection
        Dim VTran As OleDb.OleDbTransaction
        Conn = ClsInvReserveDataCarDriver.ConnectionOpen(StrConn)
        VTran = ClsInvReserveDataCarDriver.BeginTran(Conn)
        Dim rEt As Boolean = False
        With ClsInvReserveInfo
            .ReserveId = InvReserveInfo.ReserveId
            .Load(Con)
            .ReserveDate = InvReserveInfo.ReserveDate
            .UserId = InvReserveInfo.UserId
            .UnitId = InvReserveInfo.UnitId
            .AgentId = InvReserveInfo.AgentId
            .Telephone = InvReserveInfo.Telephone
            .CarAmount = InvReserveInfo.CarAmount
            .StartDate = InvReserveInfo.StartDate
            .StopDate = InvReserveInfo.StopDate
            .StatusDriver = InvReserveInfo.StatusDriver
            .HumanAmount = InvReserveInfo.HumanAmount
            .Area = InvReserveInfo.Area
            .Remark = InvReserveInfo.Remark
            .Description = InvReserveInfo.Description
            .UserStatus = InvReserveInfo.UserStatus
            .ReserveCost = InvReserveInfo.ReserveCost
            .ApproveId = InvReserveInfo.ApproveId
            .ApproveStatus = InvReserveInfo.ApproveStatus
            .ApprovedDate = InvReserveInfo.ApprovedDate
            .ApproveRemark = InvReserveInfo.ApproveRemark
            .ApproveBdStatus = InvReserveInfo.ApproveBdStatus
            .ApproveBdDate = InvReserveInfo.ApproveBdDate
            .ApproveBdRemark = InvReserveInfo.ApproveBdRemark
            .FlageReserve = InvReserveInfo.FlageReserve
            .ApproveBdId = InvReserveInfo.ApproveBdId
            .WareaGroupId = InvReserveInfo.WareaGroupId
            .DataTypeId = InvReserveInfo.DataTypeId
            .TypeId = InvReserveInfo.TypeId
            .PersonNo = InvReserveInfo.PersonNo
            rEt = .USP_Update(Conn, VTran)
        End With

        If rEt = True Then
            With ClsInvReserveDataCarDriver
                xCount = DataCarDriver.Tables(0).Rows.Count - 1
                For ii = 0 To xCount
                    If (DataCarDriver.Tables(0).Rows(ii).Item("CarId") <> "") Or _
                       (DataCarDriver.Tables(0).Rows(ii).Item("DriverId") <> "") Then
                        With ClsInvReserveDataCarDriver
                            .BdNumber = DataCarDriver.Tables(0).Rows(ii).Item("BdNumber")
                            .ReserveId = DataCarDriver.Tables(0).Rows(ii).Item("ReserveId")
                            .CostWorkStatus = "0"
                            .CarId = DataCarDriver.Tables(0).Rows(ii).Item("CarId")
                            .DriverCode = DataCarDriver.Tables(0).Rows(ii).Item("DriverId")
                            .StartMile = ""
                            .StopMile = ""
                            .WorkCost = DataCarDriver.Tables(0).Rows(ii).Item("CostWork")
                            .FuelAmount = "0"
                            .EstiMate = "0"
                            .FuelId = "0"
                        End With
                        rEt = .USP_Save(Conn, VTran)
                        If rEt = False Then
                            Exit For
                        End If
                    End If
                Next
                If rEt = True Then
                    ClsInvReserveDataCarDriver.CommitTran(VTran)
                Else
                    ClsInvReserveDataCarDriver.RollBackTran(VTran)
                End If
            End With
        Else
            ClsInvReserveDataCarDriver.RollBackTran(VTran)
        End If
        ClsInvReserveDataCarDriver.ConnectionClose(Con)
        ClsInvReserveDataCarDriver.ConnectionClose(Conn)
        Return rEt
    End Function

    Public Function Delete(ByVal InvReserveDataCarDriver As QueryInvReserveDataCarDriver, ByVal StrConn As String) As Boolean
        Dim ClsInvReserveDataCarDriver As New ClassInvReserveDataCarDriver
        Dim Conn As OleDb.OleDbConnection
        Conn = ClsInvReserveDataCarDriver.ConnectionOpen(StrConn)
        Dim ret As Boolean = False
        With ClsInvReserveDataCarDriver
            .BdNumber = InvReserveDataCarDriver.BdNumber
            .Load(Conn)
            ret = .USP_Delete(Conn)
        End With
        ClsInvReserveDataCarDriver.ConnectionClose(Conn)
        Return ret
    End Function

    Public Function Search(ByVal BdNumber As String, ByVal StrConn As String) As QueryInvReserveDataCarDrivers
        Dim ClsInvReserveDataCarDriver As New ClassInvReserveDataCarDriver
        Dim Conn As OleDb.OleDbConnection
        Conn = ClsInvReserveDataCarDriver.ConnectionOpen(StrConn)
        Dim ret As QueryInvReserveDataCarDrivers = Nothing
        Dim str As String = ""
        Dim Condition As String = ""
        str &= " Select * from InvReserveDataCarDriver"
        If BdNumber <> "" Then
            str &= " Where BdNumber='" & BdNumber & "'"
        End If
        Dim ds As DataSet
        With ClsInvReserveDataCarDriver
            ds = .BindData(Conn, , , str)
            If Not ds Is Nothing Then
                Dim i As Integer
                Dim results As New QueryInvReserveDataCarDrivers

                Try
                    For i = 0 To ds.Tables(0).Rows.Count - 1
                        With ds.Tables(0).Rows(i)

                            results.Add(New QueryInvReserveDataCarDriver( _
                              .Item("BdNumber") & "", _
                              .Item("StartMile") & "", _
                              .Item("StopMile") & "", _
                              .Item("FuelDesc") & "", _
                              .Item("FuelAmount") & "", _
                              .Item("EstiMate") & "", _
                              .Item("WorkCost") & "", _
                              .Item("CostWorkStatus") & "", _
                              .Item("ReserveId") & "", _
                              .Item("CarId") & "", _
                              .Item("DriverCode") & "", _
                              .Item("FuelId") & "" _
                            ))
                        End With
                    Next
                    ret = results
                Catch ex As Exception
                    Return Nothing
                    'Throw New DALException(ex.Message)
                End Try
            End If
        End With
        ClsInvReserveDataCarDriver.ConnectionClose(Conn)
        Return ret
    End Function
    Public Function Load(ByVal StrConn As String, Optional ByVal ParaBdNumber As String = "") As QueryInvReserveDataCarDrivers
        Dim ClsInvReserveDataCarDriver As New ClassInvReserveDataCarDriver
        Dim Conn As OleDb.OleDbConnection
        Conn = ClsInvReserveDataCarDriver.ConnectionOpen(StrConn)
        Dim ret As QueryInvReserveDataCarDrivers = Nothing
        Dim str As String = ""
        Dim Condition As String = ""
        str &= " Select * from InvReserveDataCarDriver"
        If ParaBdNumber & "" <> "" Then
            If Condition & "" <> "" Then Condition &= " and "
            Condition &= "(BdNumber = '" & ParaBdNumber & "')"
        End If

        If Condition & "" <> "" Then
            str &= " where "
            str &= Condition
        Else
            Return Nothing
            Exit Function
        End If
        Dim ds As DataSet
        With ClsInvReserveDataCarDriver
            ds = .BindData(Conn, , , str)
            If Not ds Is Nothing Then
                Dim i As Integer
                Dim results As New QueryInvReserveDataCarDrivers

                Try
                    For i = 0 To ds.Tables(0).Rows.Count - 1
                        With ds.Tables(0).Rows(i)

                            results.Add(New QueryInvReserveDataCarDriver( _
                              .Item("BdNumber") & "", _
                              .Item("StartMile") & "", _
                              .Item("StopMile") & "", _
                              .Item("FuelDesc") & "", _
                              .Item("FuelAmount") & "", _
                              .Item("EstiMate") & "", _
                              .Item("WorkCost") & "", _
                              .Item("CostWorkStatus") & "", _
                              .Item("ReserveId") & "", _
                              .Item("CarId") & "", _
                              .Item("DriverCode") & "", _
                              .Item("FuelId") & "" _
                            ))
                        End With
                    Next
                    ret = results
                Catch ex As Exception
                    Return Nothing
                    'Throw New DALException(ex.Message)
                End Try
            End If
        End With
        ClsInvReserveDataCarDriver.ConnectionClose(Conn)
        Return ret
    End Function

    Function SelectDataForCloseWork(ByVal ResevreID As String, ByVal BDNumber As String, ByVal StrConn As String) As DataSet
        Try
            Dim ClsInvReserveDataCarDriver As New ClassInvReserveDataCarDriver
            Dim Conn As OleDb.OleDbConnection
            Dim Ds As New DataSet
            Conn = ClsInvReserveDataCarDriver.ConnectionOpen(StrConn)
            Dim ret As QueryInvReserveDataCarDrivers = Nothing
            Dim StrSql As String = ""
            StrSql = "SELECT     InvReserveDataCarDriver.ReserveId, InvReserveDataCarDriver.BdNumber, ISNULL(InvDataCar.CarNumber + ' / ' + InvCarProvince.ProvinceName,'') AS CarName,"
            StrSql = StrSql & " ISNULL(InvDataDriver.DriverName,'') AS DriverName , InvReserveDataCarDriver.StartMile, InvReserveDataCarDriver.StopMile,InvReserveDataCarDriver.FuelDesc, InvReserveDataCarDriver.FuelAmount, "
            StrSql = StrSql & " InvReserveDataCarDriver.CarId, InvReserveDataCarDriver.DriverCode, InvReserveDataCarDriver.CostWorkStatus, "
            StrSql = StrSql & " InvReserveDataCarDriver.EstiMate,InvReserveDataCarDriver.WorkCost,ISNULL(InvDataCar.DataTypeId, 0) AS DataTypeId,"
            StrSql = StrSql & " ISNULL(InvDataDriver.GroupId, 0) AS GroupId, ISNULL(InvReserveDataCarDriver.FuelId,0) AS FuelId "
            StrSql = StrSql & " FROM         InvCarProvince INNER JOIN"
            StrSql = StrSql & " InvDataCar ON InvCarProvince.ProvinceId = InvDataCar.ProvinceId RIGHT OUTER JOIN"
            StrSql = StrSql & " InvReserveDataCarDriver LEFT OUTER JOIN"
            StrSql = StrSql & " InvDataDriver ON InvReserveDataCarDriver.DriverCode = InvDataDriver.DriverCode "
            StrSql = StrSql & " ON InvDataCar.CarId = InvReserveDataCarDriver.CarId "
            StrSql = StrSql & " Where ( InvReserveDataCarDriver.ReserveId = '" & ResevreID & "') And (InvReserveDataCarDriver.BdNumber = '" & BDNumber & "')"
            Ds = ClsInvReserveDataCarDriver.BindData(Conn, , , StrSql)
            ClsInvReserveDataCarDriver.ConnectionClose(Conn)
            Return Ds
        Catch ex As Exception
            Return Nothing
        End Try
    End Function


    Function SelectShowDataForCloseWork(ByVal ResevreID As String, ByVal StrConn As String) As DataSet
        Try
            Dim ClsInvReserveDataCarDriver As New ClassInvReserveDataCarDriver
            Dim Conn As OleDb.OleDbConnection
            Dim Ds As New DataSet
            Conn = ClsInvReserveDataCarDriver.ConnectionOpen(StrConn)
            Dim ret As QueryInvReserveDataCarDrivers = Nothing
            Dim StrSql As String = ""
            StrSql = " SELECT   InvReserveDataCarDriver.BdNumber, ISNULL(InvDataCar.CarNumber, '') + ' / ' + InvCarProvince.ProvinceName AS CarNumber, "
            StrSql = StrSql & " ISNULL(InvDataDriver.DriverName, '') AS DriverName, ISNULL(InvDataDriver.Telephone, '') AS Telephone, ISNULL(InvOwner_1.OwnerName, '') "
            StrSql = StrSql & " AS OwnerNameDriver, ISNULL(InvOwner.OwnerName, '') AS OwnerNameCar, InvReserveDataCarDriver.WorkCost "
            StrSql = StrSql & " FROM InvOwner RIGHT OUTER JOIN"
            StrSql = StrSql & " InvCarProvince INNER JOIN"
            StrSql = StrSql & " InvCarDataType INNER JOIN"
            StrSql = StrSql & " InvDataCar ON InvCarDataType.DataTypeID = InvDataCar.DataTypeId ON InvCarProvince.ProvinceId = InvDataCar.ProvinceId ON "
            StrSql = StrSql & " InvOwner.OwnerId = InvDataCar.OwnerId RIGHT OUTER JOIN"
            StrSql = StrSql & " InvReserveDataCarDriver LEFT OUTER JOIN"
            StrSql = StrSql & " InvDataDriver LEFT OUTER JOIN"
            StrSql = StrSql & " InvOwner InvOwner_1 ON InvDataDriver.OwnerId = InvOwner_1.OwnerId ON InvReserveDataCarDriver.DriverCode = InvDataDriver.DriverCode ON "
            StrSql = StrSql & " InvDataCar.CarId = InvReserveDataCarDriver.CarId"
            StrSql = StrSql & " Where ( InvReserveDataCarDriver.ReserveId = '" & ResevreID & "')"
            Ds = ClsInvReserveDataCarDriver.BindData(Conn, , , StrSql)
            ClsInvReserveDataCarDriver.ConnectionClose(Conn)
            Return Ds
        Catch ex As Exception
            Return Nothing
        End Try
    End Function

    Function ChangeCarDriver(ByVal StrConn As String, ByVal WareaGroup As String, ByVal rEserveId As String, ByVal BDNumber As String, ByVal StartDate As String, ByVal StopDate As String) As DataSet
        Try
            Dim ClsInvReserveDataCarDriver As New ClassInvReserveDataCarDriver
            Dim Conn As OleDb.OleDbConnection
            Dim Ds As New DataSet
            Conn = ClsInvReserveDataCarDriver.ConnectionOpen(StrConn)
            Dim ret As QueryInvReserveDataCarDrivers = Nothing
            Dim StrSql As String = ""
            StrSql &= "  select w.bdnumber,r.reserveid,r.startDate,r.stopDate,r.UserId,r.Area ,w.WorkCost ,"
            StrSql &= " c.carNumber+'/'+p.provinceName as car,d.DriverName as driver,c.CarId,d.DriverCode,"
            StrSql &= " c.OwnerStatusCar,c.DataTypeId,c.OwnerId as OwnerIdCar,d.OwnerStatusDriver,d.GroupId,d.OwnerId as OwnerIdDriver"
            StrSql &= " from invreserveinfo r inner join invreservedatacardriver w on r.reserveid=w.reserveid"
            StrSql &= " left join InvDataCar c on c.Carid=w.Carid left join InvCarProvince p on c.Provinceid=p.Provinceid"
            StrSql &= " left join InvdataDriver d on d.DriverCode=w.DriverCode"
            StrSql &= " where w.CostWorkStatus='0' "
            If WareaGroup <> "" Then
                StrSql &= " and (r.WareaGroupId='" & WareaGroup & "') "
            End If

            If rEserveId <> "" Then
                StrSql &= " and (r.reserveid like'%" & rEserveId & "%')" 
            End If
            If BDNumber <> "" Then
                StrSql &= " and (w.bdnumber like'%" & BDNumber & "%') " 
            End If
            If StartDate & "" <> "" And StopDate & "" <> "" Then
                 StrSql &= " and (Substring(r.StartDate,1,10) >= '" & StartDate & "' AND Substring(r.StartDate,1,10) <= '" & StopDate & "')"
                'StrSql &= " and (r.StartDate >= '" & StartDate & "' AND r.StartDate <= '" & StopDate & "')"
            End If

            StrSql &= "  order by w.bdnumber desc"

            Ds = ClsInvReserveDataCarDriver.BindData(Conn, , , StrSql)
            ClsInvReserveDataCarDriver.ConnectionClose(Conn)
            Return Ds
        Catch ex As Exception
            Return Nothing
        End Try
    End Function
    'Function PrintBd(ByVal StrConn As String, ByVal BdNumber As String, ByVal ReserveId As String) As DataSet
    '    Try
    '        Dim ClsInvReserveDataCarDriver As New ClassInvReserveDataCarDriver
    '        Dim Conn As OleDb.OleDbConnection
    '        Dim Ds As New DataSet
    '        Dim StrSql As String = ""
    '        Conn = ClsInvReserveDataCarDriver.ConnectionOpen(StrConn)

    '        StrSql = "SELECT InvReserveDataCarDriver.BdNumber, InvReserveDataCarDriver.ReserveId, ISNULL(InvDataCar.CarNumber + '/' + InvCarProvince.ProvinceName, '') "
    '        StrSql &= " AS CarNumber, ISNULL(InvDataDriver.DriverName, '') AS DriverName"
    '        StrSql &= " FROM InvCarProvince INNER JOIN"
    '        StrSql &= " InvDataCar ON InvCarProvince.ProvinceId = InvDataCar.ProvinceId RIGHT OUTER JOIN"
    '        StrSql &= " InvReserveDataCarDriver LEFT OUTER JOIN"
    '        StrSql &= " InvDataDriver ON InvReserveDataCarDriver.DriverCode = InvDataDriver.DriverCode ON InvDataCar.CarId = InvReserveDataCarDriver.CarId"
    '        If BdNumber <> "" Then
    '            StrSql &= " Where"
    '            StrSql &= " InvReserveDataCarDriver.BdNumber like '%" & BdNumber & "%' "
    '            If ReserveId <> "" Then
    '                StrSql &= " and InvReserveDataCarDriver.ReserveId like '%" & ReserveId & "%' "
    '            End If
    '        Else
    '            If ReserveId <> "" Then
    '                StrSql &= " Where"
    '                StrSql &= " InvReserveDataCarDriver.ReserveId like '%" & ReserveId & "%' "
    '            End If
    '        End If

    '        Ds = ClsInvReserveDataCarDriver.BindData(Conn, , , StrSql)
    '        ClsInvReserveDataCarDriver.ConnectionClose(Conn)
    '        Return Ds
    '    Catch ex As Exception
    '        Return Nothing
    '    End Try
    'End Function
    Function Search_PrintBd(ByVal StrConn As String, ByVal BdNumber As String, ByVal ReserveId As String, _
    ByVal ClostStatus As String, ByVal StartDate As String, ByVal StopDate As String) As DataSet
        Try
            Dim ClsInvReserveDataCarDriver As New ClassInvReserveDataCarDriver
            Dim Conn As OleDb.OleDbConnection
            Dim Ds As New DataSet
            Dim StrSql As String = ""
            Conn = ClsInvReserveDataCarDriver.ConnectionOpen(StrConn)

            StrSql = " SELECT InvReserveDataCarDriver.BdNumber, InvReserveDataCarDriver.ReserveId, ISNULL(InvDataCar.CarNumber + '/' + InvCarProvince.ProvinceName, '') "
            StrSql &= " AS CarNumber, ISNULL(InvDataDriver.DriverName, '') AS DriverName,InvReserveInfo.StartDate,InvReserveInfo.StopDate"
            StrSql &= " FROM InvDataDriver RIGHT OUTER JOIN"
            StrSql &= " InvReserveDataCarDriver LEFT OUTER JOIN"
            StrSql &= " InvReserveInfo ON InvReserveDataCarDriver.ReserveId = InvReserveInfo.ReserveId ON "
            StrSql &= " InvDataDriver.DriverCode = InvReserveDataCarDriver.DriverCode LEFT OUTER JOIN"
            StrSql &= " InvCarProvince INNER JOIN"
            StrSql &= " InvDataCar ON InvCarProvince.ProvinceId = InvDataCar.ProvinceId ON InvReserveDataCarDriver.CarId = InvDataCar.CarId"
            StrSql &= " WHERE InvReserveDataCarDriver.CostWorkStatus='" & ClostStatus & "'"
            If BdNumber <> "" Then
                StrSql &= " AND (InvReserveDataCarDriver.BdNumber LIKE '%" & BdNumber & "%') "
            End If
            If ReserveId <> "" Then
                StrSql &= " AND (InvReserveDataCarDriver.ReserveId LIKE '%" & ReserveId & "%')"
            End If
            If StartDate <> "" And StopDate <> "" Then
                StrSql = StrSql & " AND"
                StrSql = StrSql & "   ("
                StrSql = StrSql & "   ((Substring(InvReserveInfo.StartDate,1,10) >= '" & StartDate & "') AND (Substring(InvReserveInfo.StopDate,1,10) <= '" & StopDate & "'))"
                StrSql = StrSql & " AND "
                StrSql = StrSql & "   ((Substring(InvReserveInfo.StartDate,1,10) >= '" & StartDate & "') AND (Substring(InvReserveInfo.StopDate,1,10) <= '" & StopDate & "'))"
                StrSql = StrSql & "   )"
                'StrSql &= " AND ((convert(datetime,substring(InvReserveInfo.StartDate,0,11))) between (convert(datetime,'" & StartDate & "')) and (convert(datetime,'" & StopDate & "')) and "
                'StrSql &= " (convert(datetime,substring(InvReserveInfo.StopDate,0,11))) between (convert(datetime,'" & StartDate & "')) and (convert(datetime,'" & StopDate & "')))"
            End If
            StrSql &= " Order by InvReserveDataCarDriver.BdNumber desc"
            Ds = ClsInvReserveDataCarDriver.BindData(Conn, , , StrSql)
            ClsInvReserveDataCarDriver.ConnectionClose(Conn)
            Return Ds
        Catch ex As Exception
            Return Nothing
        End Try
    End Function
End Class